﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Microsoft.SqlServer.Server;

namespace RDBill
{
    internal class DBHelper
    {
        string con = ConfigurationManager.AppSettings["ERP"];
        SqlConnection Connection;
        public SqlConnection GetSqlConnection() {
            Connection = new SqlConnection(con);
            try
            {
                Connection.Open();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return Connection;
        }
        
        public void CloseDB() {
                 if (Connection != null) {
                  if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
        }

        public DataTable GetDataTable(string sql) {
            SqlDataAdapter adapter = new SqlDataAdapter(sql,GetSqlConnection()); 
            DataTable dataTable=new DataTable();
            try
            {
                adapter.Fill(dataTable);
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally {
                CloseDB();
            }
           return dataTable;
        }
        public int ExecuteInt(List<string> listsql) {
            bool isfalse = true;
            SqlCommand command = new SqlCommand();
            command.Connection = GetSqlConnection();
            SqlTransaction sqlTransaction= Connection.BeginTransaction();
            command.Transaction = sqlTransaction;
            command.CommandType = CommandType.Text;
            int count = -1;
            foreach (string item in listsql)
            {
                count = -1;
                try
                {
                    command.CommandText = item;
                    count= command.ExecuteNonQuery();
                    if (count <0) {
                        isfalse=false; 
                        break;
                    }
                }
                catch (Exception ex)
                {

                    isfalse = false;
                    break;
                }
            }
            if (isfalse)
            {
                sqlTransaction.Commit();
            }
            else
            {
                sqlTransaction.Rollback();
            }
            CloseDB();
            return count;
        }
        public int ExecuteInt(string sql) {
            int record = -1;
            SqlCommand command = new SqlCommand();
            command.Connection = GetSqlConnection();
            command.CommandType = CommandType.Text;
            command.CommandText = sql;
            try
            {
                record = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally {
                CloseDB();
            }
            return record;
        }
    }
}
